﻿using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.IT;
using Coldairarrow.Entity.TD;
using Coldairarrow.IBusiness;
using Coldairarrow.IBusiness.Report;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Threading.Tasks;

namespace Coldairarrow.Business.IT
{
    public partial class Report_IntroduceBusiness : BaseBusiness<IT_LocalMaterial>, IReport_IntroduceBussiness, ITransientDependency
    {
        readonly IServiceProvider _ServiceProvider;
        readonly IOperatorDept _odept;
        public Report_IntroduceBusiness(IDbAccessor db, IServiceProvider svc, IOperatorDept @odept)
            : base(db)
        {
            _ServiceProvider = svc;
            _odept = @odept;
        }

        /// <summary>
        /// 物料统计
        /// </summary>
        /// <returns></returns>

        public async Task<IntroduceDTO> GetMaterialSummary(DateTime date, string storId)
        {
            //var Base_Departmentlist = await Db.GetListBySqlAsync<Base_Department>(@"SELECT  * from Base_Department ");
            //List<string> ReturnString = new List<string>();
            //Departmentrecursion(storId, ReturnString, Base_Departmentlist);
            //string storIdinfo = string.Join(",", ReturnString.ToArray());
            List<string> listr = await _odept.GetStorString();
            string storIdinfo = string.Join(",", listr.ToArray());

            var result = new IntroduceDTO();
            var listinfo = await Db.GetListBySqlAsync<IT_LocalMaterial>(@"SELECT MaterialId FROM IT_LocalMaterial where 1=1 AND ScanNum>0 AND StorId in (" + storIdinfo + ")  GROUP BY MaterialId");
            result.Total = listinfo.Count;
            if (!storIdinfo.IsNullOrEmpty())
            {
                result.Storage = await this.GetIQueryable().Where(w => storIdinfo.Contains(w.StorId)).SumAsync(w => w.ScanNum);
            }
            return result;
        }
        ///// <summary>
        ///// 递归数据
        ///// </summary>
        ///// <param name="DepartId"></param>
        ///// <param name="liststr"></param>
        ///// <param name="listdata"></param>
        ///// <returns></returns>
        private List<string> Departmentrecursion(string DepartId, List<string> liststr, List<Base_Department> listdata)
        {
            var itemlist = listdata.Where(x => x.ParentId == DepartId).ToList();

            for (int i = 0; i < itemlist.Count; i++)
            {
                //仓库
                if (itemlist[i].Type == 2)
                {
                    liststr.Add(itemlist[i].Id);
                    Departmentrecursion(itemlist[i].Id, liststr, listdata);
                }
                else if (i + 1 == itemlist.Count)
                {
                    for (int j = 0; j < itemlist.Count; j++)
                    {
                        //if (itemlist[j].Type != 2)
                        //{
                        Departmentrecursion(itemlist[j].Id, liststr, listdata);
                        //}
                    }
                }
            }
            return liststr;
        }

        /// <summary>
        /// 入库统计
        /// </summary>
        /// <returns></returns>
        public async Task<IntroduceDTO> GetInStorageSummary(DateTime date, string storId)
        {
            string datestr = date.ToString("yyyy-MM-dd");
            List<string> listr = await _odept.GetStorString();
            string storIdinfo = string.Join(",", listr.ToArray());
            var result = new IntroduceDTO();
            //var Base_Departmentlist = await Db.GetListBySqlAsync<Base_Department>(@"SELECT  * from Base_Department ");

            //List<string> ReturnString = new List<string>();

            //Departmentrecursion(storId, ReturnString, Base_Departmentlist);
            //string storIdinfo = string.Join(",", ReturnString.ToArray());

            //string sql = "SELECT  MaterialId FROM IT_LocalMaterial WHERE  CreateTime>=STR_TO_DATE('" + startTime + "','%Y/%m/%d %H:%i:%s') AND CreateTime<=STR_TO_DATE('" + endTime + "','%Y/%m/%d %H:%i:%s') AND StorId in (" + storIdinfo + ") GROUP BY MaterialId";
            //string sqlcount = "SELECT   count(1)  jisuaninfo  from (SELECT  MaterialId FROM IT_LocalMaterial WHERE  CreateTime>=STR_TO_DATE('" + startTime + "','%Y/%m/%d %H:%i:%s') AND CreateTime<=STR_TO_DATE('" + endTime + "','%Y/%m/%d %H:%i:%s') AND StorId in (" + storIdinfo + ") GROUP BY MaterialId) a ";
            //var listinfo = Db.GetListBySqlAsync<IT_LocalMaterial>(sql).Result;
            //var infocount = Db.GetListBySqlAsync<jisuan>(sqlcount).Result.FirstOrDefault();
            //result.Total = Convert.ToDouble(infocount.jisuaninfo);
            //if (!storIdinfo.IsNullOrEmpty())
            //{
            //    result.Storage = await this.GetIQueryable().Where(w => storIdinfo.Contains(w.StorId)).Where(w => w.CreateTime >= startTime && w.CreateTime < endTime).SumAsync(w => w.Nums_N + w.Nums_O);
            //}
            //return result;

            //var infocount = Db.GetListBySqlAsync<jisuan>(@"SELECT   count(1)  jisuaninfo  from (SELECT  b.MaterialId FROM TD_InStorage a INNER JOIN TD_InStorDetail b on a.Id=b.InStorId  WHERE  a.InStorTime>=STR_TO_DATE('" + startTime + "','%Y/%m/%d %H:%i:%s') AND a.InStorTime<=STR_TO_DATE('" + endTime + "','%Y/%m/%d %H:%i:%s')  AND a.StorId in (" + storIdinfo + ")  GROUP BY MaterialId ) a ").Result.FirstOrDefault();
            //result.Total = Convert.ToDouble(infocount.jisuaninfo);
            //if (!storIdinfo.IsNullOrEmpty())
            //{

            //}
            //    result.Storage = await Db.GetIQueryable<TD_InStorage>().Where(w => storIdinfo.Contains(w.StorId)).Where(w => w.InStorTime >= startTime && w.InStorTime < endTime).SumAsync(w => w.TotalNum);

            //获取今日入库种数
            string sqlMain = @"select Count(*) as jisuaninfo from ( select b.MaterialId from TD_InStorage as a join TD_InStorRecord as b on a.Id=b.InStorId where DATE_FORMAT(a.InStorTime,'%Y-%m-%d')='" + datestr + "' and a.StorId in (" + storIdinfo + ")  GROUP BY b.MaterialId )as t ";
            var infocount = await Db.GetListBySqlAsync<jisuan>(sqlMain);
            if (infocount.Count > 0)
            {
                result.Total = Convert.ToDouble(infocount.FirstOrDefault().jisuaninfo);
            }
            else
            {
                result.Total = 0;
            }
            if (!storIdinfo.IsNullOrEmpty())
            {
                //获取今日入库数量
                sqlMain = @"select IFNULL(SUM(TotalNum),0) as jisuaninfo from TD_InStorage where DATE_FORMAT(InStorTime,'%Y-%m-%d')='" + datestr + "' AND StorId in (" + storIdinfo + ") ";
                var datanums = await Db.GetListBySqlAsync<jisuan>(sqlMain);
                if (datanums.Count > 0)
                {
                    result.Storage = Convert.ToDouble(datanums.FirstOrDefault().jisuaninfo);
                }
                else
                {
                    result.Storage = 0;
                }
            }
            return result;
        }

        /// <summary>
        /// 出库统计
        /// </summary>
        /// <returns></returns>
        public async Task<IntroduceDTO> GetOutStorageSummary(DateTime date, string storId)
        {
            //string datestr = date.ToString("yyyy-MM-dd");
            //var result = new IntroduceDTO();
            //var Base_Departmentlist = await Db.GetListBySqlAsync<Base_Department>(@"SELECT  * from Base_Department ");

            //List<string> ReturnString = new List<string>();

            //Departmentrecursion(storId, ReturnString, Base_Departmentlist);
            //string storIdinfo = string.Join(",", ReturnString.ToArray());
            string datestr = date.ToString("yyyy-MM-dd");
            List<string> listr = await _odept.GetStorString();
            string storIdinfo = string.Join(",", listr.ToArray());
            var result = new IntroduceDTO();

            //var listinfo = Db.GetListBySqlAsync<TD_OutStorDetail>(@"SELECT  MaterialId FROM TD_OutStorDetail WHERE  CreateTime>=STR_TO_DATE('" + startTime + "','%Y/%m/%d %H:%i:%s') AND CreateTime<=STR_TO_DATE('" + endTime + "','%Y/%m/%d %H:%i:%s')  AND StorId in (" + storIdinfo + ")  GROUP BY MaterialId").Result;
            //result.Total = listinfo.Count;
            //if (!storIdinfo.IsNullOrEmpty())
            //result.Storage = await Db.GetIQueryable<TD_OutStorDetail>().Where(w => storIdinfo.Contains(w.StorId)).Where(w => w.CreateTime >= startTime && w.CreateTime < endTime).SumAsync(w => w.OutNum);
            //获取今日出库种数
            string sqlMain = @"select Count(*) as jisuaninfo from ( select b.MaterialId from TD_OutStorage as a join TD_OutStorRecord as b on a.Id=b.OutStorId where DATE_FORMAT(a.OutTime,'%Y-%m-%d')='" + datestr + "' and b.StorId in (" + storIdinfo + ") and b.OutNum!=0)as t";
            var infocount = await Db.GetListBySqlAsync<jisuan>(sqlMain);
            if (infocount.Count > 0)
            {
                result.Total = Convert.ToDouble(infocount.FirstOrDefault().jisuaninfo);
            }
            else
            {
                result.Total = 0;
            }
            if (!storIdinfo.IsNullOrEmpty())
            {
                //获取今日出库数量
                sqlMain = @"select SUM(IFNULL(OutNum,0)) as jisuaninfo from TD_OutStorage where DATE_FORMAT(OutTime,'%Y-%m-%d')='" + datestr + "' and StorageId in (" + storIdinfo + ") ";
                var datanums = await Db.GetListBySqlAsync<jisuan>(sqlMain);
                if (datanums.Count > 0)
                {
                    result.Storage = Convert.ToDouble(datanums.FirstOrDefault().jisuaninfo);
                }
                else
                {
                    result.Storage = 0;
                }
            }
            return result;
        }

        /// <summary>
        /// 入库历史
        /// </summary>
        /// <returns></returns>
        public async Task<List<IntroduceHistoryDTO>> GetInStorageList(DateTime date, string storId)
        {

            //var Base_Departmentlist = await Db.GetListBySqlAsync<Base_Department>(@"SELECT  * from Base_Department ");

            //List<string> ReturnString = new List<string>();

            //Departmentrecursion(storId, ReturnString, Base_Departmentlist);
            //string storIdinfo = string.Join(",", ReturnString.ToArray());
            List<string> listr = await _odept.GetStorString();
            string storIdinfo = string.Join(",", listr.ToArray());

            //var startTime = date.Date.AddDays(-6);
            //var startTime = date.Date.AddDays(0);

            //var endTime = date.Date.AddDays(1);

            //var qTotal = from i in Db.GetIQueryable<TD_InStorage>()
            //             where i.Status == 1 && i.InStorTime >= startTime && i.InStorTime < endTime
            //             group i by EF.Functions.DateDiffDay(i.InStorTime, endTime) into g
            //             select new
            //             {
            //                 Name = "总数",
            //                 Day0 = g.Sum(a => g.Key.Equals(0) ? a.TotalNum : 0),
            //                 Day1 = g.Sum(a => g.Key.Equals(1) ? a.TotalNum : 0),
            //                 Day2 = g.Sum(a => g.Key.Equals(2) ? a.TotalNum : 0),
            //                 Day3 = g.Sum(a => g.Key.Equals(3) ? a.TotalNum : 0),
            //                 Day4 = g.Sum(a => g.Key.Equals(4) ? a.TotalNum : 0),
            //                 Day5 = g.Sum(a => g.Key.Equals(5) ? a.TotalNum : 0),
            //                 Day6 = g.Sum(a => g.Key.Equals(6) ? a.TotalNum : 0)
            //             } into gs
            //             group gs by gs.Name into s
            //             select new IntroduceHistoryDTO()
            //             {
            //                 Name = s.Key,
            //                 Day0 = s.Sum(a => a.Day0),
            //                 Day1 = s.Sum(a => a.Day1),
            //                 Day2 = s.Sum(a => a.Day2),
            //                 Day3 = s.Sum(a => a.Day3),
            //                 Day4 = s.Sum(a => a.Day4),
            //                 Day5 = s.Sum(a => a.Day5),
            //                 Day6 = s.Sum(a => a.Day6),
            //             };

            //var listTotal = await qTotal.ToListAsync();
            //if (listTotal.Count == 0) listTotal.Add(new IntroduceHistoryDTO() { Name = "总数" });

            //var qStorage = from i in Db.GetIQueryable<TD_InStorage>()
            //               where i.Status == 1 && i.InStorTime >= startTime && i.InStorTime < endTime && storIdinfo.Contains(i.StorId)
            //               group i by EF.Functions.DateDiffDay(i.InStorTime, endTime) into g
            //               select new
            //               {
            //                   Name = "仓库",
            //                   Day0 = g.Sum(a => g.Key.Equals(0) ? a.TotalNum : 0),
            //                   Day1 = g.Sum(a => g.Key.Equals(1) ? a.TotalNum : 0),
            //                   Day2 = g.Sum(a => g.Key.Equals(2) ? a.TotalNum : 0),
            //                   Day3 = g.Sum(a => g.Key.Equals(3) ? a.TotalNum : 0),
            //                   Day4 = g.Sum(a => g.Key.Equals(4) ? a.TotalNum : 0),
            //                   Day5 = g.Sum(a => g.Key.Equals(5) ? a.TotalNum : 0),
            //                   Day6 = g.Sum(a => g.Key.Equals(6) ? a.TotalNum : 0)
            //               } into gs
            //               group gs by gs.Name into s
            //               select new IntroduceHistoryDTO()
            //               {
            //                   Name = s.Key,
            //                   Day0 = s.Sum(a => a.Day0),
            //                   Day1 = s.Sum(a => a.Day1),
            //                   Day2 = s.Sum(a => a.Day2),
            //                   Day3 = s.Sum(a => a.Day3),
            //                   Day4 = s.Sum(a => a.Day4),
            //                   Day5 = s.Sum(a => a.Day5),
            //                   Day6 = s.Sum(a => a.Day6),
            //               };
            //var listStorge = await qStorage.ToListAsync();
            //if (listStorge.Count == 0) listStorge.Add(new IntroduceHistoryDTO() { Name = "仓库" });

            //listTotal.AddRange(listStorge);
            //return listTotal;
            //var infoList= String.Join(", ", ReturnString).Replace("\"", "'").Replace("\"", " ");

            //     var sql = @"
            //     SELECT
            //     	'总数' as `Name`,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=0 then TotalNum ELSE 0 END),0) as Day0,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-1 then TotalNum ELSE 0 END),0) as Day1,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-2 then TotalNum ELSE 0 END),0) as Day2,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-3 then TotalNum ELSE 0 END),0) as Day3,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-4 then TotalNum ELSE 0 END),0) as Day4,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-5 then TotalNum ELSE 0 END),0) as Day5,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-6 then TotalNum ELSE 0 END),0) as Day6
            //     from TD_InStorage
            //     WHERE 1=1 AND InStorTime BETWEEN DATE_ADD(@CurDate,INTERVAL '-8' day) and DATE_ADD(@CurDate,INTERVAL '1' day)
            //     UNION
            //     SELECT
            //     	a.`Name` as `Name`,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=0 then TotalNum ELSE 0 END),0) as Day0,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-1 then TotalNum ELSE 0 END),0) as Day1,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-2 then TotalNum ELSE 0 END),0) as Day2,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-3 then TotalNum ELSE 0 END),0) as Day3,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-4 then TotalNum ELSE 0 END),0) as Day4,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-5 then TotalNum ELSE 0 END),0) as Day5,
            //     	IFNULL(SUM(case when DATEDIFF(InStorTime,@CurDate)=-6 then TotalNum ELSE 0 END),0) as Day6
            //       from  
            // (
            //SELECT Id,`Name`  FROM Base_Department WHERE Type='2' AND Id in  (" + infoList + ") )	 a LEFT JOIN ( SELECT  *  FROM  TD_InStorage 	WHERE 1=1 AND InStorTime BETWEEN DATE_ADD(@CurDate,INTERVAL '-8' day) and DATE_ADD(@CurDate,INTERVAL '1' day)  )	  b  on a.Id=b.StorId  	GROUP BY a.`Name` ";
            //     //AND StorId=@StorId  + + ("@sqlwhere", infoList)
            //     var list = await Db.GetListBySqlAsync<IntroduceHistoryDTO>(sql, ("@CurDate", startTime));

            string sqlMain = @"select '总数' as `Name`,
 IFNULL(SUM(case  when DATE_FORMAT(InStorTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 0 DAY) THEN TotalNum else 0 end),0) as Day0,
 IFNULL(SUM(case  when DATE_FORMAT(InStorTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 1 DAY) THEN TotalNum else 0 end),0) as Day1,
 IFNULL(SUM(case  when DATE_FORMAT(InStorTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 2 DAY) THEN TotalNum else 0 end),0) as Day2,
 IFNULL(SUM(case  when DATE_FORMAT(InStorTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 3 DAY) THEN TotalNum else 0 end),0) as Day3,
 IFNULL(SUM(case  when DATE_FORMAT(InStorTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 4 DAY) THEN TotalNum else 0 end),0) as Day4,
 IFNULL(SUM(case  when DATE_FORMAT(InStorTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 5 DAY) THEN TotalNum else 0 end),0) as Day5,
 IFNULL(SUM(case  when DATE_FORMAT(InStorTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 6 DAY) THEN TotalNum else 0 end),0) as Day6
from TD_InStorage where DATE_FORMAT(InStorTime,'%Y-%m-%d') BETWEEN DATE_SUB(curdate(),INTERVAL 7 DAY) and DATE_SUB(curdate(),INTERVAL 0 DAY)
UNION
select `Name`,
SUM(IFNULL(case when InStorTime=DATE_SUB(curdate(),INTERVAL 0 DAY) THEN TotalNum ELSE 0 end,0) ) as Day0,
SUM(IFNULL(case when InStorTime=DATE_SUB(curdate(),INTERVAL 1 DAY) THEN TotalNum ELSE 0 end,0) ) as Day1,
SUM(IFNULL(case when InStorTime=DATE_SUB(curdate(),INTERVAL 2 DAY) THEN TotalNum ELSE 0 end,0) ) as Day2,
SUM(IFNULL(case when InStorTime=DATE_SUB(curdate(),INTERVAL 3 DAY) THEN TotalNum ELSE 0 end,0) ) as Day3,
SUM(IFNULL(case when InStorTime=DATE_SUB(curdate(),INTERVAL 4 DAY) THEN TotalNum ELSE 0 end,0) ) as Day4,
SUM(IFNULL(case when InStorTime=DATE_SUB(curdate(),INTERVAL 5 DAY) THEN TotalNum ELSE 0 end,0) ) as Day5,
SUM(IFNULL(case when InStorTime=DATE_SUB(curdate(),INTERVAL 6 DAY) THEN TotalNum ELSE 0 end,0) ) as Day6
 from (select a.`Name`,DATE_FORMAT(InStorTime,'%Y-%m-%d') as InStorTime,b.TotalNum from Base_Department as a left join TD_InStorage as b on a.Id=b.StorId where a.Type=2 AND a.Id in  (" + storIdinfo + ") ) as t group by `Name`";
            var list = await Db.GetListBySqlAsync<IntroduceHistoryDTO>(sqlMain);
            //, ("@StorId", storId)
            return list;
        }

        /// <summary>
        /// 出库历史
        /// </summary>
        /// <returns></returns>
        public async Task<List<IntroduceHistoryDTO>> GetOutStorageList(DateTime date, string storId)
        {
            //var Base_Departmentlist = await Db.GetListBySqlAsync<Base_Department>(@"SELECT  * from Base_Department ");

            //List<string> ReturnString = new List<string>();

            //Departmentrecursion(storId, ReturnString, Base_Departmentlist);
            //string storIdinfo = string.Join(",", ReturnString.ToArray());
            //var startTime = date.Date.AddDays(0);

            //var startTime = date.Date.AddDays(-6);
            //var endTime = date.Date.AddDays(1);
            //var qTotal = from i in Db.GetIQueryable<TD_OutStorage>()
            //             where i.Status == 1 && i.OutTime >= startTime && i.OutTime < endTime
            //             group i by EF.Functions.DateDiffDay(i.OutTime, endTime) into g
            //             select new
            //             {
            //                 Name = "总数",
            //                 Day0 = g.Sum(a => g.Key.Equals(0) ? a.OutNum : 0),
            //                 Day1 = g.Sum(a => g.Key.Equals(1) ? a.OutNum : 0),
            //                 Day2 = g.Sum(a => g.Key.Equals(2) ? a.OutNum : 0),
            //                 Day3 = g.Sum(a => g.Key.Equals(3) ? a.OutNum : 0),
            //                 Day4 = g.Sum(a => g.Key.Equals(4) ? a.OutNum : 0),
            //                 Day5 = g.Sum(a => g.Key.Equals(5) ? a.OutNum : 0),
            //                 Day6 = g.Sum(a => g.Key.Equals(6) ? a.OutNum : 0)
            //             } into gs
            //             group gs by gs.Name into s
            //             select new IntroduceHistoryDTO()
            //             {
            //                 Name = s.Key,
            //                 Day0 = s.Sum(a => a.Day0),
            //                 Day1 = s.Sum(a => a.Day1),
            //                 Day2 = s.Sum(a => a.Day2),
            //                 Day3 = s.Sum(a => a.Day3),
            //                 Day4 = s.Sum(a => a.Day4),
            //                 Day5 = s.Sum(a => a.Day5),
            //                 Day6 = s.Sum(a => a.Day6),
            //             };

            //var listTotal = await qTotal.ToListAsync();
            //if (listTotal.Count == 0) listTotal.Add(new IntroduceHistoryDTO() { Name = "总数" });

            //var qStorage = from i in Db.GetIQueryable<TD_OutStorage>()
            //               where i.Status == 1 && i.OutTime >= startTime && i.OutTime < endTime && i.StorageId == storId
            //               group i by EF.Functions.DateDiffDay(i.OutTime, endTime) into g
            //               select new
            //               {
            //                   Name = "仓库",
            //                   Day0 = g.Sum(a => g.Key.Equals(0) ? a.OutNum : 0),
            //                   Day1 = g.Sum(a => g.Key.Equals(1) ? a.OutNum : 0),
            //                   Day2 = g.Sum(a => g.Key.Equals(2) ? a.OutNum : 0),
            //                   Day3 = g.Sum(a => g.Key.Equals(3) ? a.OutNum : 0),
            //                   Day4 = g.Sum(a => g.Key.Equals(4) ? a.OutNum : 0),
            //                   Day5 = g.Sum(a => g.Key.Equals(5) ? a.OutNum : 0),
            //                   Day6 = g.Sum(a => g.Key.Equals(6) ? a.OutNum : 0)
            //               } into gs
            //               group gs by gs.Name into s
            //               select new IntroduceHistoryDTO()
            //               {
            //                   Name = s.Key,
            //                   Day0 = s.Sum(a => a.Day0),
            //                   Day1 = s.Sum(a => a.Day1),
            //                   Day2 = s.Sum(a => a.Day2),
            //                   Day3 = s.Sum(a => a.Day3),
            //                   Day4 = s.Sum(a => a.Day4),
            //                   Day5 = s.Sum(a => a.Day5),
            //                   Day6 = s.Sum(a => a.Day6),
            //               };
            //var listStorge = await qStorage.ToListAsync();
            //if (listStorge.Count == 0) listStorge.Add(new IntroduceHistoryDTO() { Name = "仓库" });

            //listTotal.AddRange(listStorge);
            //return listTotal;
            //            var startTime = date.Date;
            //var infoList = String.Join(", ", ReturnString).Replace("\"", "'").Replace("\"", " ");

            //     var sql = @"
            //      SELECT
            //     	'总数' as `Name`,
            //     	IFNULL(SUM(case when DATEDIFF(OutTime, @CurDate) = 0 then OutNum ELSE 0 END), 0) as Day0,
            //     	IFNULL(SUM(case when DATEDIFF(OutTime, @CurDate) = -1 then OutNum ELSE 0 END), 0) as Day1,
            //     	IFNULL(SUM(case when DATEDIFF(OutTime, @CurDate) = -2 then OutNum ELSE 0 END), 0) as Day2,
            //     	IFNULL(SUM(case when DATEDIFF(OutTime, @CurDate) = -3 then OutNum ELSE 0 END), 0) as Day3,
            //     	IFNULL(SUM(case when DATEDIFF(OutTime, @CurDate) = -4 then OutNum ELSE 0 END), 0) as Day4,
            //     	IFNULL(SUM(case when DATEDIFF(OutTime, @CurDate) = -5 then OutNum ELSE 0 END), 0) as Day5,
            //     	IFNULL(SUM(case when DATEDIFF(OutTime, @CurDate) = -6 then OutNum ELSE 0 END), 0) as Day6
            //     from TD_OutStorage
            //     WHERE 1=1 AND OutTime BETWEEN DATE_ADD(@CurDate,INTERVAL '-8' day) and DATE_ADD(@CurDate,INTERVAL '1' day)
            //     UNION
            //     SELECT
            //     	a.`Name` as `Name`,
            //     	IFNULL(SUM(case when DATEDIFF(b.OutTime, @CurDate) = 0 then OutNum ELSE 0 END), 0) as Day0,
            //     	IFNULL(SUM(case when DATEDIFF(b.OutTime, @CurDate) = -1 then OutNum ELSE 0 END), 0) as Day1,
            //     	IFNULL(SUM(case when DATEDIFF(b.OutTime, @CurDate) = -2 then OutNum ELSE 0 END), 0) as Day2,
            //     	IFNULL(SUM(case when DATEDIFF(b.OutTime, @CurDate) = -3 then OutNum ELSE 0 END), 0) as Day3,
            //     	IFNULL(SUM(case when DATEDIFF(b.OutTime, @CurDate) = -4 then OutNum ELSE 0 END), 0) as Day4,
            //     	IFNULL(SUM(case when DATEDIFF(b.OutTime, @CurDate) = -5 then OutNum ELSE 0 END), 0) as Day5,
            //     	IFNULL(SUM(case when DATEDIFF(b.OutTime, @CurDate) = -6 then OutNum ELSE 0 END), 0) as Day6
            //       from  
            // (
            //SELECT Id,`Name`  FROM Base_Department WHERE Type='2' AND Id in  (" + infoList + ") )	 a LEFT JOIN ( SELECT  *  FROM  TD_OutStorage 	WHERE  1=1 AND OutTime BETWEEN DATE_ADD(@CurDate,INTERVAL '-8' day) and DATE_ADD(@CurDate,INTERVAL '1' day)  )	  b  on a.Id=b.StorageId  	GROUP BY a.`Name` ";
            //     // `Status`= 1
            //     var list = await Db.GetListBySqlAsync<IntroduceHistoryDTO>(sql, ("@CurDate", startTime));
            List<string> listr = await _odept.GetStorString();
            string storIdinfo = string.Join(",", listr.ToArray());
            string sqlMain = @"
select '总数' as `Name`,
 IFNULL(SUM(case  when DATE_FORMAT(OutTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 0 DAY) THEN OutNum else 0 end),0) as Day0,
 IFNULL(SUM(case  when DATE_FORMAT(OutTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 1 DAY) THEN OutNum else 0 end),0) as Day1,
 IFNULL(SUM(case  when DATE_FORMAT(OutTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 2 DAY) THEN OutNum else 0 end),0) as Day2,
 IFNULL(SUM(case  when DATE_FORMAT(OutTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 3 DAY) THEN OutNum else 0 end),0) as Day3,
 IFNULL(SUM(case  when DATE_FORMAT(OutTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 4 DAY) THEN OutNum else 0 end),0) as Day4,
 IFNULL(SUM(case  when DATE_FORMAT(OutTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 5 DAY) THEN OutNum else 0 end),0) as Day5,
 IFNULL(SUM(case  when DATE_FORMAT(OutTime,'%Y-%m-%d')=DATE_SUB(curdate(),INTERVAL 6 DAY) THEN OutNum else 0 end),0) as Day6
from TD_OutStorage where DATE_FORMAT(OutTime,'%Y-%m-%d') BETWEEN DATE_SUB(curdate(),INTERVAL 7 DAY) and DATE_SUB(curdate(),INTERVAL 0 DAY)
UNION
select `Name`,
SUM(IFNULL(case when OutTime=DATE_SUB(curdate(),INTERVAL 0 DAY) THEN OutNum ELSE 0 end,0) ) as Day0,
SUM(IFNULL(case when OutTime=DATE_SUB(curdate(),INTERVAL 1 DAY) THEN OutNum ELSE 0 end,0) ) as Day1,
SUM(IFNULL(case when OutTime=DATE_SUB(curdate(),INTERVAL 2 DAY) THEN OutNum ELSE 0 end,0) ) as Day2,
SUM(IFNULL(case when OutTime=DATE_SUB(curdate(),INTERVAL 3 DAY) THEN OutNum ELSE 0 end,0) ) as Day3,
SUM(IFNULL(case when OutTime=DATE_SUB(curdate(),INTERVAL 4 DAY) THEN OutNum ELSE 0 end,0) ) as Day4,
SUM(IFNULL(case when OutTime=DATE_SUB(curdate(),INTERVAL 5 DAY) THEN OutNum ELSE 0 end,0) ) as Day5,
SUM(IFNULL(case when OutTime=DATE_SUB(curdate(),INTERVAL 6 DAY) THEN OutNum ELSE 0 end,0) ) as Day6
 from (select a.`Name`,DATE_FORMAT(OutTime,'%Y-%m-%d') as OutTime,b.OutNum from Base_Department as a left join TD_OutStorage as b on a.Id=b.StorageId where a.Type=2 AND a.Id in  (" + storIdinfo + ") ) as t group by `Name`";
            var list = await Db.GetListBySqlAsync<IntroduceHistoryDTO>(sqlMain);
            return list;
        }
    }
}